package edu.unika.aifb.graphindex.importer.db.metadata;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Set;

import net.disy.cadenza.associations.schema.Association;
import net.disy.cadenza.associations.schema.AssociationList;
import net.disy.cadenza.associations.schema.AttributeReference;
import net.disy.cadenza.dbmapping.schema.AttributeMapping;
import net.disy.cadenza.dbmapping.schema.DatabaseMapping;
import net.disy.cadenza.dbmapping.schema.Source;
import net.disy.cadenza.objecttypes.schema.Attribute;
import net.disy.cadenza.objecttypes.schema.CadenzaObjectType;
import net.disy.cadenza.repository.wrappers.ObjectTypeCatalogWrapper;

import org.apache.log4j.Logger;

public class DatabaseFactory {
	private final static Logger log = Logger.getLogger(DatabaseFactory.class);

	public static Database createDatabase(Connection conn, String schema){
		Database database = new Database();
		try {
			DatabaseMetaData dbMetadata =  conn.getMetaData();
			ResultSet tablesMetadata = dbMetadata.getTables(null, schema, null, new String[]{"TABLE"});
			
			while(tablesMetadata.next()){
				String tableName = tablesMetadata.getString("TABLE_NAME");
				String tableSchema = tablesMetadata.getString("TABLE_SCHEM");
				if(tableSchema == null)
					tableSchema = tablesMetadata.getString("TABLE_CAT");
				Table table = new Table(tableName);
				table.setSchema(tableSchema);
				ResultSet tableColumnsMetadata = dbMetadata.getColumns(null, null, tableName, null);
				ResultSet tablePrimaryKeyColumnsMetadata = dbMetadata.getPrimaryKeys(null, null, tableName);
				
				while(tableColumnsMetadata.next()){
					Column column = new Column(table, tableColumnsMetadata.getString("COLUMN_NAME"));
//					column.setOrdinalPosition(tableColumnsMetadata.getInt("ORDINAL_POSITION"));
					column.setDataType(tableColumnsMetadata.getInt("DATA_TYPE"));
					table.addColumn(column);
				}
				
				while(tablePrimaryKeyColumnsMetadata.next()){
					table.getColumn(tablePrimaryKeyColumnsMetadata.getString("COLUMN_NAME")).setPrimaryKey(true);
				}
				database.addTable(table);
			}
			
			tablesMetadata = dbMetadata.getTables(null, schema, null, new String[]{"TABLE"});
			while(tablesMetadata.next()){
				String tableName = tablesMetadata.getString("TABLE_NAME");
				ResultSet tableImportedKeysMetadata =  dbMetadata.getImportedKeys(null, schema, tableName);
				while(tableImportedKeysMetadata.next()){
					Column tableColumn = database.getTable(tableName).getColumn(tableImportedKeysMetadata.getString("FKCOLUMN_NAME"));
					Table foreignTable = database.getTable(tableImportedKeysMetadata.getString("PKTABLE_NAME"));
					if(foreignTable == null){
						log.debug(tableImportedKeysMetadata.getString("PKTABLE_NAME") + " does not exist in schema " + schema);
						continue;
					}
					Column foreignTableColumn = foreignTable.getColumn(tableImportedKeysMetadata.getString("PKCOLUMN_NAME"));
					tableColumn.getTable().addForeignKeyConstraint(tableImportedKeysMetadata.getString("FK_NAME"), tableColumn, foreignTableColumn);
				}
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		return database;
	}
	
	public static Database createDatabase(ObjectTypeCatalogWrapper objectTypeCatalog, Set<String> objectTypeNames){
		Database database = new Database();
		try {
			for(String objectTypeName : objectTypeNames){
				CadenzaObjectType objectType = objectTypeCatalog.getObjectType(objectTypeName);
				DatabaseMapping databaseMapping = (DatabaseMapping)objectType.getMappings().getAny();
				String tableName = databaseMapping.getTable();
				Table table = database.getTable(tableName);
				boolean isNewTable = false;
				if(table == null){
					table = new Table(tableName);
					isNewTable = true;
				}
				
				if(databaseMapping.isSetSchema())
					table.setSchema(databaseMapping.getSchema());
				for(AttributeMapping attributeMapping : databaseMapping.getAttributeMapping()){
					Source source =  attributeMapping.getSource();
					if(source == null){
						continue;
					}
					String columnName = source.getColumn();
					if(table.getColumn(columnName)== null){
						Column column = new Column(table, columnName);
						for(Attribute attribute : objectType.getObjectTypeStructure().getAttributeList().getAttribute()){
							if(attribute.getName().equalsIgnoreCase(attributeMapping.getAttributeName())){
								column.setType(attribute.getType());
								if(attribute.isSetKey() && attribute.isKey())
									column.setPrimaryKey(true);
								column.setVisible(attribute.getVisualization().isSetVisible() && attribute.getVisualization().isVisible());
							}
						}
						table.addColumn(column);
					}
				}
				
				if(isNewTable && table.getColumns().size() > 0)
					database.addTable(table);
			}
			
//			setTablesPrimaryKeys(connections, database.getTables());
			
			int fkId = 1;
			
			for(AssociationList associationList : objectTypeCatalog.getAssociationLists()){
				for (Association association : associationList.getAssociation()) {
					String firstObjectTypeName = association.getFirstType().getObjectTypeName();
					String secondObjectTypeName = association.getSecondType().getObjectTypeName();
					if(!objectTypeNames.contains(firstObjectTypeName) || !objectTypeNames.contains(secondObjectTypeName)){
						continue;
					}
					DatabaseMapping firstDatabaseMapping = objectTypeCatalog.getDatabaseMapping(firstObjectTypeName);
					DatabaseMapping secondDatabaseMapping = objectTypeCatalog.getDatabaseMapping(secondObjectTypeName);
					if(firstDatabaseMapping == null || secondDatabaseMapping == null)
						continue;
					Table firstTable = database.getTable(firstDatabaseMapping.getTable());
					Table secondTable = database.getTable(secondDatabaseMapping.getTable());
					
					if(firstTable == null || secondTable == null)
						continue;
					
					int refAttrSize = association.getFirstType().getAttributeReference().size();
					
					for(int i = 0; i < association.getFirstType().getAttributeReference().size(); i++){
						
						AttributeReference firstObjectAttrRef = association.getFirstType().getAttributeReference().get(i);
						AttributeReference secondObjectAttrRef = association.getSecondType().getAttributeReference().get(i);
						String firstObjectRefAttrName = firstObjectAttrRef.getAttributeName();
						String secondObjectRefAttrName = secondObjectAttrRef.getAttributeName();
						boolean isFirstOptional = association.isFirstOptional();
						
						for(AttributeMapping secondAttributeMapping : secondDatabaseMapping.getAttributeMapping()){
							if(secondAttributeMapping.getAttributeName().equalsIgnoreCase(secondObjectRefAttrName)){
								Source source = secondAttributeMapping.getSource();
								if(source != null){
									String secondTableColumnName = source.getColumn();
									Column secondTableColumn = secondTable.getColumn(secondTableColumnName);
									if(secondTableColumn.isPrimaryKey() || secondTableColumn.getName().toLowerCase().endsWith("id") || refAttrSize == 1){
										for(AttributeMapping firstAttributeMapping : firstDatabaseMapping.getAttributeMapping()){
											if(firstAttributeMapping.getAttributeName().equalsIgnoreCase(firstObjectRefAttrName)){
												source = firstAttributeMapping.getSource();
												if(source != null){
													String firstTableColumnName = source.getColumn();
													Column firstTableColumn = firstTable.getColumn(firstTableColumnName);
													String fkName = "FK" + (fkId++);
													if(!isFirstOptional){
														//String fkName = String.format("%s_%s_to_%s_%s", firstTable.getName(), firstTableColumnName, secondTable.getName(), secondTableColumnName );
														firstTable.addForeignKeyConstraint(fkName, firstTableColumn, secondTableColumn);
													}else{
														//String fkName = String.format("%s_%s_to_%s_%s", secondTable.getName(), secondTableColumnName, firstTable.getName(), firstTableColumnName);
														secondTable.addForeignKeyConstraint(fkName, secondTableColumn, firstTableColumn);
													}
												}
											}
										}
									}
								}	
							}
						}
					}
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return database;
	}
	
//	private static void setTablesPrimaryKeys(List<Connection> connections, List<Table> tables){
//		List<DatabaseMetaData> databaseMetaData = new ArrayList<DatabaseMetaData>();
//		for(Connection connection : connections){
//			try {
//				databaseMetaData.add(connection.getMetaData());
//			} catch (SQLException e) {
//				continue;
//			}
//		}
//		for(Table table : tables){
//			String tableName = table.getName();
//			String schema = table.getSchema();
//			for(DatabaseMetaData dbMetadata : databaseMetaData){
//				try {
//					ResultSet tablesMetadata = dbMetadata.getTables(null, schema, tableName, new String[]{"TABLE", "VIEW"});
//					if(tablesMetadata.next()){
//						ResultSet tablePrimaryKeyColumnsMetadata = dbMetadata.getPrimaryKeys(null, null, tableName);
//						while(tablePrimaryKeyColumnsMetadata.next()){
//							Column primaryKeyColumn = table.getColumn(tablePrimaryKeyColumnsMetadata.getString("COLUMN_NAME"));
//							if(primaryKeyColumn != null)
//								primaryKeyColumn.setPrimaryKey(true);
//						}
//						break;
//					}
//				} catch (SQLException e) {
//					continue;
//				}
//			}
//		}
//	}
	
	public static void saveDatabase(Database database, String outputFileName){
		try {
			FileOutputStream f_out = new FileOutputStream(outputFileName);
			ObjectOutputStream obj_out = new ObjectOutputStream (f_out);
			obj_out.writeObject ( database );
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Database restoreDatabase(String databaseFile){
		try {
			FileInputStream f_in = new FileInputStream(databaseFile);
			ObjectInputStream obj_in = new ObjectInputStream (f_in);
			return (Database)obj_in.readObject();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}
